﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using CNCSoft.EWinform.ERPApps;
using CNCSoft.EData;
using System.Data.SqlClient;

using Common;

namespace CNCSoft.ErpApps.TMS.Academic
{
    public partial class frmXepLop : Form
    {

        DataTable dtLopCu;
        DataTable dtLopMoi;
        bool ChuyenMoi = false;

        public frmXepLop()
        {
            InitializeComponent();
        }

        private void frmXepLop_Load(object sender, EventArgs e)
        {
            txtMaNienHocCu.Text = GlobalVariable.WSInfo.getAppProperty("MaNienHocDefault");
            txtMaNienHoc.Text = GlobalVariable.WSInfo.getAppProperty("MaNienHocMoi");
            txtNgayVaoLop.Text = GlobalVariable.WSInfo.WorkingDate.Date.ToString("dd/MM/yyyy");

            grid_LopCu.DataSource = dtLopCu;
            grid_LopMoi.DataSource = dtLopMoi;

            DataTable dt_CheDo = new DataTable();
            SQLHelper.Fill(dt_CheDo, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_CHEDO");
            repositoryItemLookUpEdit_MaCheDo.DataSource = dt_CheDo;
            DataTable dt_TrangThai = new DataTable();
            SQLHelper.Fill(dt_TrangThai, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_TRANGTHAI");
            repositoryItemLookUpEditTrangThai.DataSource = dt_TrangThai;

        }

        private void txtMaNienHocCu_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtMaNienHocCu.Text == "") return;

                DataTable dt = new DataTable();
                SQLHelper.Fill(dt, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_LOP WHERE MaNienHoc = '" + txtMaNienHocCu.Text + "' Order by MaKhoi, STT");

                cboLopCu.DataSource = dt;
                cboLopCu.DisplayMember = "TenLop";
                cboLopCu.ValueMember = "MaLop";
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void txtMaNienHoc_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtMaNienHoc.Text == "") return;
                DataTable dt = new DataTable();
                SQLHelper.Fill(dt, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_LOP WHERE MaNienHoc = '" + txtMaNienHoc.Text + "' Order by MaKhoi, STT");

                cboLop.DataSource = dt;
                cboLop.DisplayMember = "TenLop";
                cboLop.ValueMember = "MaLop";
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void cboLopCu_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (cboLopCu.SelectedIndex == -1) return;
                dtLopCu = new DataTable();
                SQLHelper.Fill(dtLopCu, GlobalVariable.WSInfo.ConnectionString, "SELECT MaHocTap, HOCSINH_LOP.MaHocSinh, MaLop, HoLot + ' ' + Ten As HoTen, MaCheDo, MaTrangThai, " +
                                                                            "STT_SoCai, getdate() as NgayVaoLop, '' as MaLopCu, SoDuNamTruoc " + 
                                                   "FROM HOCSINH_LOP " +
                                                   "JOIN HOCSINH ON HOCSINH_LOP.MaHocSinh = HOCSINH.MaHocSinh  " +
                                                   "WHERE MaLop = '" + cboLopCu.SelectedValue + "' Order by STT_SoCai");
                grid_LopCu.DataSource = dtLopCu;
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void cmdChuyenLop_Click(object sender, EventArgs e)
        {
            try
            {
                dtLopMoi = dtLopCu;
                grid_LopMoi.DataSource = dtLopMoi;
                for (int i = 0; i < dtLopMoi.Rows.Count; i++)
                {
                    dtLopMoi.Rows[i]["MaLop"] = cboLop.SelectedValue;
                    dtLopMoi.Rows[i]["MaHocTap"] = dtLopMoi.Rows[i]["MaHocSinh"] + "_" + cboLop.SelectedValue;
                    dtLopMoi.Rows[i]["NgayVaoLop"] = txtNgayVaoLop.getDateValue().Date;
                    dtLopMoi.Rows[i]["MaLopCu"] = cboLopCu.SelectedValue;
                    //Set trang thai addnew
                    dtLopMoi.Rows[i].AcceptChanges();
                    dtLopMoi.Rows[i].SetAdded();
                }
                ChuyenMoi = true;
                //gridColumn_MaLopCu.UnGroup();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void cboLop_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (cboLop.SelectedIndex == -1) return;
                dtLopMoi = new DataTable();
                SQLHelper.Fill(dtLopMoi, GlobalVariable.WSInfo.ConnectionString, "SELECT MaHocTap, HOCSINH_LOP.MaHocSinh, MaLop, HoLot + ' ' + Ten As HoTen, MaCheDo, MaTrangThai, " + 
                                                                             "STT_SoCai, NgayVaoLop, MaLopCu, SoDuNamTruoc " +
                                                   "FROM HOCSINH_LOP " +
                                                   "JOIN HOCSINH ON HOCSINH_LOP.MaHocSinh = HOCSINH.MaHocSinh  " +
                                                   "WHERE MaLop = '" + cboLop.SelectedValue + "' Order by Malop, MalopCu, STT_SoCai");
                grid_LopMoi.DataSource = dtLopMoi;

                ChuyenMoi = false;
                //gridColumn_MaLopCu.Group();
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }


        private void DuyetRowState()
        {
            if (dtLopMoi == null || dtLopMoi.Rows.Count == 0)
                return;
            try
            {
                //dataTable.AcceptChanges();

                foreach (DataRow row in dtLopMoi.Rows)
                    //if (row["MaHocTap"] == DBNull.Value)
                    {
                        row.AcceptChanges();
                        row.SetAdded();
                    }
            }
            catch { }
        }

        private void cmdCapNhat_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection cnn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);

                if (ChuyenMoi)
                {
                    SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "DELETE FROM HOCSINH_LOP WHERE MaLopCu = '" + cboLopCu.SelectedValue + "'");
                }



                string sqlInsert, sqlUpdate, sqlDelete;
                sqlInsert = "INSERT INTO HOCSINH_LOP(MaHocTap, MaHocSinh, MaLop, MaCheDo, MaTrangThai, STT_SoCai, NgayVaoLop, MaLopCu, SoDuNamTruoc) " +
                                              " VALUES(@pMaHocTap, @pMaHocSinh, @pMaLop, @pMaCheDo, @pMaTrangThai, @pSTT_SoCai, @pNgayVaoLop, @pMaLopCu, @pSoDuNamTruoc " +
                                              ") ";
                sqlUpdate = "UPDATE HOCSINH_LOP SET MaHocSinh=@pMaHocSinh, MaLop=@pMaLop, MaCheDo=@pMaCheDo, MaTrangThai=@pMaTrangThai, STT_SoCai=@pSTT_SoCai, " +
                                                   "NgayVaoLop=@pNgayVaoLop, MaLopCu=@pMaLopCu, SoDuNamTruoc=@pSoDuNamTruoc " +
                                   " WHERE MaHocTap=@pMaHocTap";

                sqlDelete = "DELETE FROM HOCSINH_LOP WHERE MaHocTap=@pMaHocTap";

                SqlParameter[] pInsert = new SqlParameter[9];
                SqlParameter[] pUpdate = new SqlParameter[9];
                SqlParameter[] pDelete = new SqlParameter[1];
                
                pInsert[0] = new SqlParameter("@pMaHocTap", SqlDbType.VarChar, 50, "MaHocTap");
                pInsert[1] = new SqlParameter("@pMaHocSinh", SqlDbType.VarChar, 20, "MaHocSinh");
                pInsert[2] = new SqlParameter("@pMaLop", SqlDbType.VarChar, 20, "MaLop");
                pInsert[3] = new SqlParameter("@pMaCheDo", SqlDbType.VarChar, 2, "MaCheDo");
                pInsert[4] = new SqlParameter("@pMaTrangThai", SqlDbType.VarChar, 2, "MaTrangThai");
                pInsert[5] = new SqlParameter("@pSTT_SoCai", SqlDbType.Int, 18, "STT_SoCai");
                pInsert[6] = new SqlParameter("@pNgayVaoLop", SqlDbType.Date, 10, "NgayVaoLop");
                pInsert[7] = new SqlParameter("@pMaLopCu", SqlDbType.VarChar, 20, "MaLopCu");
                pInsert[8] = new SqlParameter("@pSoDuNamTruoc", SqlDbType.Money, 18, "SoDuNamTruoc");

                pUpdate[0] = new SqlParameter("@pMaHocSinh", SqlDbType.VarChar, 20, "MaHocSinh");
                pUpdate[1] = new SqlParameter("@pMaLop", SqlDbType.VarChar, 20, "MaLop");
                pUpdate[2] = new SqlParameter("@pMaCheDo", SqlDbType.VarChar, 2, "MaCheDo");
                pUpdate[3] = new SqlParameter("@pMaTrangThai", SqlDbType.VarChar, 2, "MaTrangThai");
                pUpdate[4] = new SqlParameter("@pSTT_SoCai", SqlDbType.Int, 18, "STT_SoCai");
                pUpdate[5] = new SqlParameter("@pNgayVaoLop", SqlDbType.Date, 10, "NgayVaoLop");
                pUpdate[6] = new SqlParameter("@pMaLopCu", SqlDbType.VarChar, 20, "MaLopCu");
                pUpdate[7] = new SqlParameter("@pSoDuNamTruoc", SqlDbType.Money, 18, "SoDuNamTruoc");
                pUpdate[8] = new SqlParameter("@pMaHocTap", SqlDbType.VarChar, 50, "MaHocTap");

                pDelete[0] = new SqlParameter("@pMaHocTap", SqlDbType.VarChar, 50, "MaHocTap");

                SqlCommand cmdInsert = new SqlCommand(sqlInsert, cnn);
                SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, cnn);
                SqlCommand cmdDelete = new SqlCommand(sqlDelete, cnn);

                cmdInsert.Parameters.AddRange(pInsert);
                cmdUpdate.Parameters.AddRange(pUpdate);
                cmdDelete.Parameters.AddRange(pDelete);

                SqlDataAdapter da = new SqlDataAdapter();
                da.InsertCommand = cmdInsert;
                da.UpdateCommand = cmdUpdate;
                da.DeleteCommand = cmdDelete;
                try
                {
                    da.Update(dtLopMoi);
                    dtLopMoi.AcceptChanges();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                //Load lai
                cboLop_SelectedIndexChanged(null, null);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        


        
    }
}
